﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
using System.Text;
using WPP_Service.Data;
using System.IO;
using System.Configuration;
using Newtonsoft.Json;
using System.Data.SqlClient;
using System.Collections;

namespace WPP_Service
{
    // Start the service and browse to http://<machine_name>:<port>/Service1/help to view the service's generated help page
    // NOTE: By default, a new instance of the service is created for each call; change the InstanceContextMode to Single if you want
    // a single instance of the service to process all calls.	
    [ServiceContract]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall)]
    // NOTE: If the service is renamed, remember to update the global.asax.cs file
    public class WPPService
    {
        #region POST order

        /// <summary>
        /// Recieves an OrderQuery object from the Stream and passes it to the helper method.
        /// </summary>
        /// <param name="streamdata">Reads a response body.</param>
        /// <returns>The filtered array of orders matching the criteria specified in the request.</returns>
        [WebInvoke(Method = "POST", UriTemplate = "order")]
        public Order[] GetOrders(Stream streamdata)
        {
            Order[] orders = new Order[1];
            InvokeResponse response = new InvokeResponse();
            response.ResponseCode = InvokeResponseCode.INVOKE_OK;
            response.ServiceVersion = ConfigurationManager.AppSettings["ServiceVersion"];
            response.ResponseString = "POST_OK";
            response.ErrorMessage = "";

            //create a stream reader with parameter stream
            StreamReader reader = new StreamReader(streamdata);
            string res = String.Empty;
            try
            {
                res = reader.ReadToEnd();
            }
            catch (IOException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_IO_ERROR;
                response.ResponseString = "POST_IO_ERROR";
                response.ErrorMessage = e.ToString();
                orders[0] = new Order();
                orders[0].InvokeResponse = response;
                return orders;
            }
            reader.Close();
            reader.Dispose();
            //create a new order query object
            OrderQuery obj = null;
            try
            {
                 //add data to query object
                obj = (OrderQuery)JsonConvert.DeserializeObject(res, typeof(OrderQuery));
            }
            catch (JsonSerializationException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_DESERIALIZATION_ERROR;
                response.ResponseString = "POST_DESERIALIZATION_ERROR";
                response.ErrorMessage = e.ToString();
                orders[0] = new Order();
                orders[0].InvokeResponse = response;
                return orders;
            }
            catch (JsonReaderException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_JSON_READER_ERROR;
                response.ResponseString = "POST_JSON_READER_ERROR";
                response.ErrorMessage = e.ToString();
                orders[0] = new Order();
                orders[0].InvokeResponse = response;
                return orders;
            }
            //send object to helper method
            orders = _GetOrders(obj);

            return orders;
        }

        /// <summary>
        /// Helper method for the GetOrders method. This enables the service to call the method internally
        /// without having to stream a request.
        /// </summary>
        /// <param name="obj">The filter criteria.</param>
        /// <returns>The filtered array of orders matching the criteria specified in the obj parameter.</returns>
        public Order[] _GetOrders(OrderQuery obj)
        {
            Order[] orders = new Order[1];
            InvokeResponse response = new InvokeResponse();
            response.ResponseCode = InvokeResponseCode.INVOKE_OK;
            response.ServiceVersion = ConfigurationManager.AppSettings["ServiceVersion"];
            response.ResponseString = "POST_OK";
            response.ErrorMessage = "";

            //create two lists of strings so subqueries can be easily added
            List<string> orderQuery = new List<string>();
            List<string> packageQuery = new List<string>();
               
            //for each date in orderQuery get the corrosponding date information
            if (obj.Dates != null)
            {
                foreach (DateGroup i in obj.Dates)
                {

                    if ((int)i.DateType == 0)
                    {
                        orderQuery.Add(" o.order_process_date > '" + i.StartDate.ToShortDateString() + "' and o.order_process_date < '" + i.EndDate.ToShortDateString() + "' ");
                    }

                    if ((int)i.DateType == 1)
                    {
                        orderQuery.Add(" o.order_creation_date > '" + i.StartDate.ToShortDateString() + "' and o.order_creation_date < '" + i.EndDate.ToShortDateString()+ "' ");
                    }

                    if ((int)i.DateType == 2)
                    {
                        orderQuery.Add(" o.order_transaction_date > '" + i.StartDate.ToShortDateString() + "' and o.order_transaction_date < '" + i.EndDate.ToShortDateString()+"' ");
                    }

                    if ((int)i.DateType == 3)
                    {
                        packageQuery.Add(" p.package_date_boxed > '" + i.StartDate.ToShortDateString() + "' and p.package_date_boxed < '" + i.EndDate.ToShortDateString() + "' and p.Order_id = o.Order_id");
                    }

                    if ((int)i.DateType == 4)
                    {
                        packageQuery.Add(" p.package_pickup_date > '" + i.StartDate.ToShortDateString() + "' and p.package_pickup_date < '" + i.EndDate.ToShortDateString() + "' and p.Order_id = o.Order_id");
                    }
                }
            }
            
            //for every other item in OrderQuery check to see if the 
            //element contains data, if it does add it to the query
            if (obj.ShipName != null)
                orderQuery.Add(" o.order_shipping_name LIKE '%" + obj.ShipName + "%'  ");
 
            
            if (obj.BillName != null)
                orderQuery.Add(" o.order_billing_name LIKE '%" + obj.BillName + "%'  ");
        

           if (obj.Phone != null)
               orderQuery.Add(" o.phone = " + obj.Phone );

           if (obj.PackageNumber != 0)
               packageQuery.Add(" p.package_number = " + obj.PackageNumber);

           if (obj.OrderNumber != 0)
               orderQuery.Add(" o.order_number = " + obj.OrderNumber);
         

            if (obj.Complete != null)
            {
                if (obj.Complete == true)
                    packageQuery.Add(" o.complete = 1");
                else
                     packageQuery.Add(" o.complete = 0");
            }
         
            if (obj.Multibox != null)
            {
                if (obj.Multibox == true)
                    packageQuery.Add(" p.multi_box = 1");
                else
                    packageQuery.Add(" p.multi_box = 0");               
            }
           
            if (obj.Cancelled != null)
            {
                if (obj.Cancelled == true)
                     packageQuery.Add( " p.cancelled = 1");
                else
                     packageQuery.Add( " p.cancelled = 0");
                
            }
           
            if (obj.Boxed != null)
            {
                if (obj.Multibox == true)
                    packageQuery.Add(" p.boxed = 1 ");
                else
                     packageQuery.Add( " p.boxed = 0 ");
                              
            }
           

            //turn the lists into arrays
           string [] orderQueryArray = orderQuery.ToArray();
           string [] packageQueryArray = packageQuery.ToArray();
        //create a variable for page number
           int pageNum = obj.Page;
           int resperpage = obj.ResPerPage;
            //start adding information to the string
           string queryString = "DECLARE @rowNum INT = " + (pageNum-1) * resperpage + ";" +
                "SELECT  Order_id " +
                " FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY Order_id ASC) AS Row, Order_id " +
                " FROM    (SELECT distinct o.Order_id FROM Orders o FULL OUTER JOIN Package p ON o.Order_id = p.Order_id WHERE o.cleared = 0 AND "; 
                //add an and in between different search criteria (order Query)
                for (int i = 0; i < orderQueryArray.Length; i++)
                {
                    if (i != 0)               
                         queryString += " AND " + orderQueryArray[i];
                   
                    else
                         queryString += " " + orderQueryArray[i];                  
                    
                }

                //add an and in between different search criteria (package Query)
                if (orderQueryArray.Length != 0 && packageQueryArray.Length != 0)
                    queryString += " AND ";

                for (int i = 0; i < packageQueryArray.Length; i++)
                {
                    if (i != 0)                  
                         queryString += " AND " + packageQueryArray[i];
                  
                    else                 
                        queryString += " " + packageQueryArray[i];
            
                }

            queryString +=") as a) tmp "+
                            "WHERE   Row > @rowNum AND Row <= @rowNum + " + obj.ResPerPage;

            List<int> orderids = new List<int>();
            try
            {
                //connect to the database
                using (SqlConnection sqlServerConnection = new SqlConnection("Data Source=141.165.12.207;Initial Catalog=WPPRO;User Id=sa;Password=Gr0g@n1t3$;Pooling=False"))
                {
                    sqlServerConnection.Open();
                    SqlCommand sqlServerCommand;
                    //execute the query using query string
                    using (sqlServerCommand = new SqlCommand( queryString, sqlServerConnection))
                    {
                        using (SqlDataReader reader = sqlServerCommand.ExecuteReader())
                        {
                            while(reader.Read())
                            {
                                 //add unique Order_id's to the orderids List   
                                int currentid = (int)reader["Order_id"];
                                if (!orderids.Contains(currentid)) orderids.Add(currentid);
                            }
                            orderids.Sort();
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_SQL_ERROR;
                response.ResponseString = "POST_SQL_ERROR";
                response.ErrorMessage = e.ToString();
                orders[0] = new Order();
                orders[0].InvokeResponse = response;
                return orders;
            }
            catch (NullReferenceException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_NULL_MEMBER_ERROR;
                response.ResponseString = "POST_NULL_MEMBER_ERROR";
                response.ErrorMessage = e.ToString();
                orders[0] = new Order();
                orders[0].InvokeResponse = response;
                return orders;
            }
            //assign order id list to Orders
            orders = GetOrders(orderids.ToArray());

            return orders;
        }

        #endregion

        #region POST clear

        /// <summary>
        /// Recieves an Order object from the Stream and passes it to the helper method.
        /// </summary>
        /// <param name="streamdata">Reads a response body.</param>
        /// <returns>A response from the service detailing the sucecess of the call.</returns>
        [WebInvoke(Method = "POST", UriTemplate = "clear")]
        public InvokeResponse BatchClear(Stream streamdata)
        {
            InvokeResponse response = new InvokeResponse();
            response.ResponseCode = InvokeResponseCode.INVOKE_OK;
            response.ServiceVersion = ConfigurationManager.AppSettings["ServiceVersion"];
            response.ResponseString = "POST_OK";
            response.ErrorMessage = "";

            StreamReader reader = new StreamReader(streamdata);
            string res = String.Empty;
            try
            {
                res = reader.ReadToEnd();
            }
            catch (IOException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_IO_ERROR;
                response.ResponseString = "POST_IO_ERROR";
                response.ErrorMessage = e.ToString();
                //return response;
            }
            reader.Close();
            reader.Dispose();

            DateGroup obj = null;
            try
            {
                obj = (DateGroup)JsonConvert.DeserializeObject(res, typeof(DateGroup));
            }
            catch (JsonSerializationException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_DESERIALIZATION_ERROR;
                response.ResponseString = "POST_DESERIALIZATION_ERROR";
                response.ErrorMessage = e.ToString();
                //return response;
            }
            catch (JsonReaderException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_JSON_READER_ERROR;
                response.ResponseString = "POST_JSON_READER_ERROR";
                response.ErrorMessage = e.ToString();
                //return response;
            }

            try
            {
                    //connect to database
                using (SqlConnection sqlServerConnection = new SqlConnection("Data Source=141.165.12.207;Initial Catalog=WPPRO;User Id=sa;Password=Gr0g@n1t3$;Pooling=False"))
                {
                    sqlServerConnection.Open();
                    SqlCommand sqlServerCommand;

                    if (obj.DateType == DateType.BATCH_CLEAR)
                    {
                            //set orders to be cleared = 1
                        using (sqlServerCommand = new SqlCommand("UPDATE Orders SET cleared = 1 WHERE order_creation_date >= '" + obj.StartDate.ToShortDateString() + "' AND order_creation_date <= '" + obj.EndDate.ToShortDateString() + "';", sqlServerConnection))
                        {
                            sqlServerCommand.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_SQL_ERROR;
                response.ResponseString = "POST_SQL_ERROR";
                response.ErrorMessage = e.ToString();
                return response;
            }
            catch (NullReferenceException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_NULL_MEMBER_ERROR;
                response.ResponseString = "POST_NULL_MEMBER_ERROR";
                response.ErrorMessage = e.ToString();
                return response;
            }

            return response;
        }

        #endregion

        #region PUT order

        /// <summary>
        /// Recieves an Order object from the Stream and passes it to the helper method.
        /// </summary>
        /// <param name="streamdata">Reads a response body.</param>
        /// <returns>A response from the service detailing the sucecess of the call.</returns>
        [WebInvoke(Method = "PUT", UriTemplate = "order")]
        public InvokeResponse UpdateOrder(Stream streamdata)
        {
            InvokeResponse response = new InvokeResponse();
            response.ResponseCode = InvokeResponseCode.INVOKE_OK;
            response.ServiceVersion = ConfigurationManager.AppSettings["ServiceVersion"];
            response.ResponseString = "POST_OK";
            response.ErrorMessage = "";

            StreamReader reader = new StreamReader(streamdata);
            string res = String.Empty;
            try
            {
                res = reader.ReadToEnd();
            }
            catch (IOException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_IO_ERROR;
                response.ResponseString = "POST_IO_ERROR";
                response.ErrorMessage = e.ToString();
                //return response;
            }
            reader.Close();
            reader.Dispose();

            Order obj = null;
            try
            {
                obj = (Order)JsonConvert.DeserializeObject(res, typeof(Order));
            }
            catch (JsonSerializationException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_DESERIALIZATION_ERROR;
                response.ResponseString = "POST_DESERIALIZATION_ERROR";
                response.ErrorMessage = e.ToString();
                //return response;
            }
            catch (JsonReaderException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_JSON_READER_ERROR;
                response.ResponseString = "POST_JSON_READER_ERROR";
                response.ErrorMessage = e.ToString();
                //return response;
            }

            response = _UpdateOrder(obj);

            return response;
        }

        /// <summary>
        /// Helper method for the UpdateOrder method. This enables the service to call the method internally
        /// without having to stream a request.
        /// </summary>
        /// <param name="obj">The updated Order object.</param>
        /// <returns>A response from the service detailing the sucecess of the call.</returns>
        public InvokeResponse _UpdateOrder(Order obj)
        {
            InvokeResponse response = new InvokeResponse();
            response.ResponseCode = InvokeResponseCode.INVOKE_OK;
            response.ServiceVersion = ConfigurationManager.AppSettings["ServiceVersion"];
            response.ResponseString = "POST_OK";
            response.ErrorMessage = "";

            try
            {
                    //connect to database
                using (SqlConnection sqlServerConnection = new SqlConnection("Data Source=141.165.12.207;Initial Catalog=WPPRO;User Id=sa;Password=Gr0g@n1t3$;Pooling=False"))
                {
                    sqlServerConnection.Open();
                    SqlCommand sqlServerCommand;

                    short t = 0;
                    if (obj.Complete) t = 1;
                    //whenever an order has not been completed, set the value to null so Min date is not displayed
                    if (obj.OrderCompleteDate == DateTime.MinValue)
                    {
                        using (sqlServerCommand = new SqlCommand("UPDATE Orders SET complete = " + t + ", clear order_complete_date = NULL WHERE Order_id = " + obj.OrderID + ";", sqlServerConnection))
                        {
                            sqlServerCommand.ExecuteNonQuery();
                        }
                    }
                    else
                    {
                        using (sqlServerCommand = new SqlCommand("UPDATE Orders SET complete = " + t + ", order_complete_date = '" + obj.OrderCompleteDate + "' WHERE Order_id = " + obj.OrderID + ";", sqlServerConnection))
                        {
                            sqlServerCommand.ExecuteNonQuery();
                        }
                    }

                    foreach (Package p in obj.Packages) _UpdatePackage(p);
                }
            }
            catch (SqlException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_SQL_ERROR;
                response.ResponseString = "POST_SQL_ERROR";
                response.ErrorMessage = e.ToString();
                return response;
            }
            catch (NullReferenceException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_NULL_MEMBER_ERROR;
                response.ResponseString = "POST_NULL_MEMBER_ERROR";
                response.ErrorMessage = e.ToString();
                return response;
            }

            return response;
        }

        #endregion

        #region PUT package

        /// <summary>
        /// Recieves a Package object from the Stream and passes it to the helper method.
        /// </summary>
        /// <param name="streamdata">Reads a response body.</param>
        /// <returns>A response from the service detailing the sucecess of the call.</returns>
        [WebInvoke(Method = "PUT", UriTemplate = "package")]
        public InvokeResponse UpdatePackage(Stream streamdata)
        {
            InvokeResponse response = new InvokeResponse();
            response.ResponseCode = InvokeResponseCode.INVOKE_OK;
            response.ServiceVersion = ConfigurationManager.AppSettings["ServiceVersion"];
            response.ResponseString = "POST_OK";
            response.ErrorMessage = "";

            StreamReader reader = new StreamReader(streamdata);
            string res = String.Empty;
            try
            {
                res = reader.ReadToEnd();
            }
            catch (IOException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_IO_ERROR;
                response.ResponseString = "POST_IO_ERROR";
                response.ErrorMessage = e.ToString();
                return response;
            }
            reader.Close();
            reader.Dispose();

            Package obj = null;
            try
            {
                obj = (Package)JsonConvert.DeserializeObject(res, typeof(Package));
            }
            catch (JsonSerializationException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_DESERIALIZATION_ERROR;
                response.ResponseString = "POST_DESERIALIZATION_ERROR";
                response.ErrorMessage = e.ToString();
                return response;
            }
            catch (JsonReaderException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_JSON_READER_ERROR;
                response.ResponseString = "POST_JSON_READER_ERROR";
                response.ErrorMessage = e.ToString();
                return response;
            }

            response = _UpdatePackage(obj);

            return response;
        }

        /// <summary>
        /// Helper method for the UpdatePackage method. This enables the service to call the method internally
        /// without having to stream a request.
        /// </summary>
        /// <param name="obj">The updated Package object.</param>
        /// <returns>A response from the service detailing the sucecess of the call.</returns>
        public InvokeResponse _UpdatePackage(Package obj)
        {
            InvokeResponse response = new InvokeResponse();
            response.ResponseCode = InvokeResponseCode.INVOKE_OK;
            response.ServiceVersion = ConfigurationManager.AppSettings["ServiceVersion"];
            response.ResponseString = "POST_OK";
            response.ErrorMessage = "";

            try
            {
                using (SqlConnection sqlServerConnection = new SqlConnection("Data Source=141.165.12.207;Initial Catalog=WPPRO;User Id=sa;Password=Gr0g@n1t3$;Pooling=False"))
                {
                    sqlServerConnection.Open();
                    SqlCommand sqlServerCommand;

                    short t = 0;
                    if (obj.Complete) t = 1;

                    // Only lets the client change the complete column.
                    using (sqlServerCommand = new SqlCommand("UPDATE Package SET complete = " + t + " WHERE Package_id = " + obj.PackageID + ";", sqlServerConnection))
                    {
                        sqlServerCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_SQL_ERROR;
                response.ResponseString = "POST_SQL_ERROR";
                response.ErrorMessage = e.ToString();
                return response;
            }
            catch (NullReferenceException e)
            {
                response.ResponseCode = InvokeResponseCode.INVOKE_NULL_MEMBER_ERROR;
                response.ResponseString = "POST_NULL_MEMBER_ERROR";
                response.ErrorMessage = e.ToString();
                return response;
            }

            return response;
        }

        #endregion

        #region Subroutines

        public Order[] GetOrders(int[] ids)
        {
            if (ids.Length == 0)
            {
                return new Order[0];
            }
            //for each order id in the array select all data from orders and packages
            string query_begin = "SELECT o.Order_id, order_shipping_name, order_process_date, order_billing_name, order_creation_date, order_complete_date, order_transaction_date, o.complete as order_complete, o.cleared, phone, order_number, p.Package_id, package_pickup_date, package_date_boxed, p.boxed, p.multi_box, p.complete, p.cancelled, p.package_number, oi.OrderItem_id, item_number, item_possku, item_description, item_customer_notes, item_quantity FROM Orders o JOIN Package p ON o.Order_id = p.Order_id JOIN OrderItem oi ON p.Package_id = oi.Package_id WHERE ";
            string query_end = " ORDER BY o.Order_id, p.Package_id, oi.OrderItem_id ASC";
            for (int i = 0; i < ids.Length; i++)
            {
                //if it is not the last order, add the order id with an or seperating them
                if (i != ids.Length - 1) query_begin += "o.Order_id = " + ids[i] + " OR ";
                //the last order need no "OR" after it
                else query_begin += "o.Order_id = " + ids[i];
            }
            string query = query_begin + query_end;

            List<Order> orderlist = new List<Order>();
            List<Package> packagelist = new List<Package>();
            List<OrderItem> itemlist = new List<OrderItem>();
            
            //connect to database
            using (SqlConnection sqlServerConnection = new SqlConnection("Data Source=141.165.12.207;Initial Catalog=WPPRO;User Id=sa;Password=Gr0g@n1t3$;Pooling=False"))
            {
                sqlServerConnection.Open();
                SqlCommand sqlServerCommand;

                using (sqlServerCommand = new SqlCommand(query, sqlServerConnection))
                {
                    using (SqlDataReader reader = sqlServerCommand.ExecuteReader())
                    {
                        int current_order = 0, previous_order = 0, current_package = 0, previous_package = 0;
                        Order o = null;
                        Package p = null;
                        OrderItem oi = null;
                        while (reader.Read())
                        {
                            o = new Order();
                            p = new Package();
                            oi = new OrderItem();
                            current_order = (int)reader["Order_id"];
                            current_package = (int)reader["Package_id"];

                            //set all the data for the current order
                            if (current_order != previous_order)
                            {
                                o.OrderID = (int)reader["Order_id"];
                                o.OrderShippingName = reader["order_shipping_name"].ToString();
                                if (reader["order_process_date"] != DBNull.Value) o.OrderProcessDate = DateTime.SpecifyKind(Convert.ToDateTime(reader["order_process_date"]), DateTimeKind.Utc);
                                o.OrderBillingName = reader["order_billing_name"].ToString();
                                if (reader["order_creation_date"] != DBNull.Value) o.OrderCreationDate = DateTime.SpecifyKind(Convert.ToDateTime(reader["order_creation_date"]), DateTimeKind.Utc);
                                if (reader["order_complete_date"] != DBNull.Value) o.OrderCompleteDate = DateTime.SpecifyKind(Convert.ToDateTime(reader["order_complete_date"]), DateTimeKind.Utc);
                                if (reader["order_transaction_date"] != DBNull.Value) o.OrderTransactionDate = DateTime.SpecifyKind(Convert.ToDateTime(reader["order_transaction_date"]), DateTimeKind.Utc);
                                o.Complete = Convert.ToBoolean(reader["order_complete"]);
                                o.Phone = reader["phone"].ToString();
                                o.OrderNumber = reader["order_number"].ToString();
                                orderlist.Add(o);
                                packagelist.Clear();
                                o = new Order();
                            }
                            //set all the package data for the curent order
                            if (current_package != previous_package)
                            {
                                p.PackageID = (int)reader["Package_id"];
                                p.PackagePickupDate = DateTime.SpecifyKind(Convert.ToDateTime(reader["package_pickup_date"]), DateTimeKind.Utc);
                                p.PackageDateBoxed = DateTime.SpecifyKind(Convert.ToDateTime(reader["package_date_boxed"]), DateTimeKind.Utc);
                                p.MultiBox = Convert.ToBoolean(reader["multi_box"]);
                                p.Complete = Convert.ToBoolean(reader["complete"]);
                                p.Cancelled = Convert.ToBoolean(reader["cancelled"]);
                                p.PackageNumber = reader["package_number"].ToString();
                                packagelist.Add(p);
                                orderlist[orderlist.Count - 1].Packages = packagelist.ToArray();
                                itemlist.Clear();
                                p = new Package();
                            }                            
                            //set all the order item data for the current package
                            oi.OrderItemID = (int)reader["OrderItem_id"];
                            oi.ItemNumber = reader["item_number"].ToString();
                            oi.ItemPossku = reader["item_possku"].ToString();
                            oi.ItemDescription = reader["item_description"].ToString();
                            oi.ItemCustomerNotes = reader["item_customer_notes"].ToString();
                            oi.ItemQuantity = (int)reader["item_quantity"];
                            itemlist.Add(oi);
                            packagelist[packagelist.Count - 1].OrderItems = itemlist.ToArray();
                            
                            //update the current order and package
                            previous_package = current_package;
                            previous_order = current_order;
                        }
                    }
                }
            }

            return orderlist.ToArray();
        }

        #endregion
    }   
}
